package org.ccu.eshop.dao;

import org.ccu.eshop.domain.dto.CategoryVO;
import org.ccu.eshop.domain.dto.MerchandiseVo;
import org.ccu.eshop.utils.ComboPooledDataSourceUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MerchandiseDao {
    /**
     * 根据是否特价查询商品列表
     * @param special
     * @return
     */

    public List<MerchandiseVo> selectMerchantListBySpecial(int special,String query) {
        List<MerchandiseVo> merchandiseList=new ArrayList<>();
        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{
           String sql="";
            if("all".equals(query)){
                //2-1获得承接sql的容器对象
                 sql="select id,category,merName,price,sprice,merModel,picture,merDesc,manufacturer,leaveFactoryDate,special from merchandise WHERE special=? " ;
            }else{
                //2-1获得承接sql的容器对象
                sql="select id,category,merName,price,sprice,merModel,picture,merDesc,manufacturer,leaveFactoryDate,special from merchandise WHERE special=? " +
                        "LIMIT 3";
            }


            //2-2获得sql语句
            PreparedStatement ps=connection.prepareStatement(sql);
            //2-3执行sql语句
             ps.setInt(1,special);
            ResultSet set=ps.executeQuery();

            while (set.next()){
                MerchandiseVo vo=new MerchandiseVo( set.getInt("id"),set.getInt("category"),set.getString("merName"),set.getBigDecimal("price"),set.getBigDecimal("sprice"),set.getString("merModel"),set.getString("picture"),set.getString("merDesc"),set.getString("manufacturer"),set.getString("leaveFactoryDate"),set.getInt("special"));
                merchandiseList.add(vo);
            }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return merchandiseList;
    }

    public MerchandiseVo selectMerchantById(int id) {
        MerchandiseVo vo=null;
        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{

            //2-1获得承接sql的容器对象
            String sql="select id,category,merName,price,sprice,merModel,picture,merDesc,manufacturer,DATE_FORMAT(leaveFactoryDate,'%Y-%m-%d') leaveFactoryDate,special from merchandise WHERE id=?";
            //2-2获得sql语句
            PreparedStatement ps=connection.prepareStatement(sql);
            //2-3执行sql语句
            ps.setInt(1,id);
            ResultSet set=ps.executeQuery();
            if(set.next()){
                vo=new MerchandiseVo( set.getInt("id"), set.getInt("category"),
                        set.getString("merName"),set.getBigDecimal("price"),
                        set.getBigDecimal("sprice"),set.getString("merModel"),
                        set.getString("picture"),set.getString("merDesc"),
                        set.getString("manufacturer"),set.getString("leaveFactoryDate"),
                        set.getInt("special"));
            }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
            return vo;
    }
}
